/*
 * Copyright (c) 2019.  drakelee.base All rights reserved.
 */

package com.base.components.database.dao.base;

import com.base.components.common.util.SqlLikeHelper;
import com.base.components.database.dao.base.condition.ConditionEnum;
import org.apache.commons.collections4.map.SingletonMap;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.Assert;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.HashMap;
import java.util.Map;

/**
 * 构建原生sql动态条件
 *
 * @author <a href="drakelee1221@gmail.com">LiGeng</a>
 * @version v1.0.0
 * @date 2019-01-18 10:57
 */
public class NativeSQLBuilder {
  private StringBuilder whereSql = new StringBuilder();
  private String querySql = "";
  private Map<String, Object> paramMap = new HashMap<>();
  private String orderBy = "";
  private String groupBy = "";
  private String having = "";
  /** 当前默认连接符（AND或OR） */
  private String currentLink = "";
  public NativeSQLBuilder(){
  }
  /** 使用当前默认连接符 AND，调用addWhere之前可以不再调用 and() */
  public static NativeSQLBuilder defaultAnd(){
    return new NativeSQLBuilder().linkAnd();
  }
  /** 使用当前默认连接符 AND，调用addWhere之前可以不再调用 and() */
  public static NativeSQLBuilder defaultAnd(String querySql){
    return new NativeSQLBuilder().linkAnd().bindQuerySql(querySql);
  }
  /** 使用当前默认连接符 OR，调用addWhere之前可以不再调用 or() */
  public static NativeSQLBuilder defaultOr(){
    return new NativeSQLBuilder().linkOr();
  }
  /** 使用当前默认连接符 OR，调用addWhere之前可以不再调用 or() */
  public static NativeSQLBuilder defaultOr(String querySql){
    return new NativeSQLBuilder().linkOr().bindQuerySql(querySql);
  }

  /**
   * 增加条件，默认验证 value 不能为 blank，例： t.user_name = :user_name
   * @param prefix  - nonnull - 字段前缀，不用带点，例： t
   * @param field   - nonnull - 字段名，例： user_name
   * @param value   - nonnull - 字段值，例： 张三
   * @param opFlag  - nonnull - 连接符，例： ConditionEnum.OPERATE_EQUAL
   */
  public NativeSQLBuilder addWhere(String prefix, String field, Object value, ConditionEnum opFlag) {
    return addWhere(prefix, field, value, opFlag, true, null);
  }

  /**
   * 增加条件，例： t.user_name = :user_name
   * @param prefix    - nonnull - 字段前缀，不用带点，例： t
   * @param field     - nonnull - 字段名，例： user_name
   * @param value     - nonnull - 字段值，例： 张三
   * @param opFlag    - nonnull - 连接符，例： ConditionEnum.OPERATE_EQUAL
   * @param notBlank  - nonnull - 验证 value 是否不为 blank
   */
  public NativeSQLBuilder addWhere(String prefix, String field, Object value, ConditionEnum opFlag, boolean notBlank) {
    return addWhere(prefix, field, value, opFlag, notBlank, null);
  }

  /**
   * 增加条件，默认验证 value 不能为 blank，例： t.user_name = :user_name
   * @param prefix     - nonnull - 字段前缀，不用带点，例： t
   * @param field      - nonnull - 字段名，例： user_name
   * @param value      - nonnull - 字段值，例： 张三
   * @param opFlag     - nonnull - 连接符，例： ConditionEnum.OPERATE_EQUAL
   * @param fieldAlias - nullable - 字段赋值别名，用在多个相同字段，不同条件的情况
   */
  public NativeSQLBuilder addWhere(String prefix, String field, Object value, ConditionEnum opFlag, String fieldAlias) {
    return addWhere(prefix, field, value, opFlag, true, fieldAlias);
  }

  /**
   * 增加条件，例： t.user_name = :${fieldAlias}
   * @param prefix     - nonnull - 字段前缀，不用带点，例： t
   * @param field      - nonnull - 字段名，例： user_name
   * @param value      - nonnull - 字段值，例： 张三
   * @param opFlag     - nonnull - 连接符，例： ConditionEnum.OPERATE_EQUAL
   * @param notBlank   - nonnull - 验证 value 是否不为 blank
   * @param fieldAlias - nullable - 字段赋值别名，用在多个相同字段，不同条件的情况
   */
  public NativeSQLBuilder addWhere(String prefix, String field, Object value, ConditionEnum opFlag, boolean notBlank, String fieldAlias) {
    if(notBlank && (value == null || StringUtils.isBlank(value.toString())) ){
      return this;
    }
    String paramField = field;
    if(StringUtils.isNotBlank(fieldAlias)){
      paramField = fieldAlias;
    }
    String pre = "";
    if(StringUtils.isNotBlank(prefix)){
      pre = prefix + ".";
    }
    switch (opFlag) {
      case OPERATE_IS_NOT_NULL:
        whereSql.append(currentLink).append(pre).append(field).append(" IS NOT NULL ");
        break;
      case OPERATE_IS_NULL:
        whereSql.append(currentLink).append(pre).append(field).append(" IS NULL ");
        break;
      case OPERATE_EQUAL:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" =:").append(paramField);
          setParameter(paramField, value);
        }
        break;
      case OPERATE_UNEQUAL:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" !=:").append(paramField);
          setParameter(paramField, value);
        }
        break;
      case OPERATE_GREATER:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" >:").append(paramField);
          setParameter(paramField, value);
        }
        break;
      case OPERATE_LESS:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" <:").append(paramField);
          setParameter(paramField, value);
        }
        break;
      case OPERATE_GREATER_EQUAL:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" >=:").append(paramField);
          setParameter(paramField, value);
        }
        break;
      case OPERATE_LESS_EQUAL:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" <=:").append(paramField);
          setParameter(paramField, value);
        }
        break;
      case OPERATE_IN:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" IN (:").append(paramField).append(")");
          setParameter(paramField, value);
        }
        break;
      case OPERATE_NOT_IN:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" NOT IN (:").append(paramField).append(")");
          setParameter(paramField, value);
        }
        break;
      case OPERATE_LIKE:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" LIKE :").append(paramField);
          setParameter(paramField, SqlLikeHelper.leftAndRight(value.toString()));
        }
        break;
      case OPERATE_LEFT_LIKE:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" LIKE :").append(paramField);
          setParameter(paramField, SqlLikeHelper.left(value.toString()));
        }
        break;
      case OPERATE_RIGHT_LIKE:
        if (value != null) {
          whereSql.append(currentLink).append(pre).append(field).append(" LIKE :").append(paramField);
          setParameter(paramField, SqlLikeHelper.right(value.toString()));
        }
        break;
      default:
        break;
    }
    return this;
  }
  /** 设置当前默认连接符为空，调用addWhere前需手动调用 or() 或 and() */
  public NativeSQLBuilder linkClean() {
    currentLink = "";
    return this;
  }

  /** 设置当前默认连接符 OR，调用addWhere之前可以不再调用 or() */
  public NativeSQLBuilder linkOr() {
    currentLink = " OR ";
    return this;
  }

  /** 设置当前默认连接符 AND，调用addWhere之前可以不再调用 and() */
  public NativeSQLBuilder linkAnd() {
    currentLink = " AND ";
    return this;
  }

  /** 手动直接添加连接符 OR，将当前默认连接符设置为空 */
  public NativeSQLBuilder or() {
    currentLink = "";
    whereSql.append(" OR ");
    return this;
  }

  /** 手动直接添加连接符 AND，将当前默认连接符设置为空 */
  public NativeSQLBuilder and() {
    currentLink = "";
    whereSql.append(" AND ");
    return this;
  }

  /** 手动直接添加分组连接符 OR (，将当前默认连接符设置为空 */
  public NativeSQLBuilder orGroupStart() {
    currentLink = "";
    whereSql.append(" OR (");
    return this;
  }

  /** 手动直接添加分组连接符 AND (，将当前默认连接符设置为空 */
  public NativeSQLBuilder andGroupStart() {
    currentLink = "";
    whereSql.append(" AND (");
    return this;
  }

  public NativeSQLBuilder groupStart() {
    whereSql.append(" ( ");
    return this;
  }

  public NativeSQLBuilder groupEnd() {
    whereSql.append(" ) ");
    return this;
  }
  public NativeSQLBuilder orderBy(String orderBySql) {
    this.orderBy = orderBySql;
    return this;
  }
  public NativeSQLBuilder groupBy(String groupBySql) {
    this.groupBy = groupBySql;
    return this;
  }
  public NativeSQLBuilder having(String havingSql) {
    this.having = havingSql;
    return this;
  }
  public Query build(EntityManager entityManager) {
    return buildFrom(entityManager, null);
  }

  /** 推荐使用 buildFrom */
  @Deprecated
  public Query build(EntityManager entityManager,String includeSql, String placeholder) {
    String temp = includeSql.replace(placeholder, querySql + this.whereSql.toString());
    if(StringUtils.isNotBlank(groupBy)){
      temp+=" GROUP BY "+this.groupBy;
    }
    if(StringUtils.isNotBlank(having)){
      temp+=" HAVING "+this.having;
    }
    if(StringUtils.isNotBlank(orderBy)){
      temp+=" ORDER BY "+this.orderBy;
    }
    Query nativeQuery = entityManager.createNativeQuery(temp);
    for (Map.Entry en : paramMap.entrySet()) {
      nativeQuery.setParameter(en.getKey().toString(), en.getValue());
    }
    return nativeQuery;
  }

  /**
   * 构建 Query，通过其它 builder来替换当前 sql 里面的占位符
   * @param entityManager -
   * @param replaceBuilderMap - key=替换当前sql里面的占位符，value=对应的其它builder
   * @return Query
   */
  public Query buildFrom(EntityManager entityManager, Map<String, NativeSQLBuilder> replaceBuilderMap) {
    String sql = concatSql();
    if(replaceBuilderMap != null){
      for (Map.Entry<String, NativeSQLBuilder> entry : replaceBuilderMap.entrySet()) {
        NativeSQLBuilder other = entry.getValue();
        for (Map.Entry<String, Object> param : other.getParamMap().entrySet()) {
          setParameter(param.getKey(), param.getValue());
        }
        sql = sql.replace(entry.getKey(), other.concatSql());
      }
    }
    Query nativeQuery = entityManager.createNativeQuery(sql);
    for (Map.Entry en : paramMap.entrySet()) {
      nativeQuery.setParameter(en.getKey().toString(), en.getValue());
    }
    return nativeQuery;
  }

  public String concatSql(){
    StringBuilder sb = new StringBuilder(this.querySql).append(this.whereSql);
    concat(sb);
    return sb.toString();
  }

  public void setParameter(String key, Object value){
    Assert.isTrue(StringUtils.isNotBlank(key), "key can not be null");
    Assert.isTrue(value != null, "value can not be null");
    checkParam(key, value);
    this.paramMap.put(key, value);
  }

  public Map<String, Object> getParamMap() {
    return paramMap;
  }

  public NativeSQLBuilder bindQuerySql(String querySql) {
    this.querySql = querySql;
    return this;
  }

  public Map<String, NativeSQLBuilder> toReplaceMap(String placeholder){
    return new SingletonMap<>(placeholder, this);
  }

  private void concat(StringBuilder sb){
    if(StringUtils.isNotBlank(groupBy)){
      sb.append(" GROUP BY ").append(this.groupBy);
    }
    if(StringUtils.isNotBlank(having)){
      sb.append(" HAVING ").append(this.having);
    }
    if(StringUtils.isNotBlank(orderBy)){
      sb.append(" ORDER BY ").append(this.orderBy);
    }
  }
  
  private void checkParam(String key, Object value){
    Object oldVal = this.paramMap.get(key);
    if(oldVal != null && oldVal.equals(value)){
      throw new IllegalArgumentException("设置参数值重复，key = " + key + ", oldVal = " + oldVal + ", newVal = " + value);
    }
  }

  public NativeSQLBuilder addWhereIsNotNull(String prefix, String field) {
    return addWhere(prefix, field, null, ConditionEnum.OPERATE_IS_NOT_NULL, false);
  }
  public NativeSQLBuilder addWhereIsNull(String prefix, String field) {
    return addWhere(prefix, field, null, ConditionEnum.OPERATE_IS_NULL, false);
  }

  public NativeSQLBuilder addWhereEq(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_EQUAL);
  }
  public NativeSQLBuilder addWhereEq(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_EQUAL, notBlank);
  }
  public NativeSQLBuilder addWhereEq(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_EQUAL, fieldAlias);
  }
  public NativeSQLBuilder addWhereEq(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_EQUAL, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereNotEq(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_UNEQUAL);
  }
  public NativeSQLBuilder addWhereNotEq(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_UNEQUAL, notBlank);
  }
  public NativeSQLBuilder addWhereNotEq(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_UNEQUAL, fieldAlias);
  }
  public NativeSQLBuilder addWhereNotEq(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_UNEQUAL, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereGe(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER);
  }
  public NativeSQLBuilder addWhereGe(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER, notBlank);
  }
  public NativeSQLBuilder addWhereGe(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER, fieldAlias);
  }
  public NativeSQLBuilder addWhereGe(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereLe(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS);
  }
  public NativeSQLBuilder addWhereLe(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS, notBlank);
  }
  public NativeSQLBuilder addWhereLe(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS, fieldAlias);
  }
  public NativeSQLBuilder addWhereLe(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereGeEq(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER_EQUAL);
  }
  public NativeSQLBuilder addWhereGeEq(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER_EQUAL, notBlank);
  }
  public NativeSQLBuilder addWhereGeEq(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER_EQUAL, fieldAlias);
  }
  public NativeSQLBuilder addWhereGeEq(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_GREATER_EQUAL, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereLeEq(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS_EQUAL);
  }
  public NativeSQLBuilder addWhereLeEq(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS_EQUAL, notBlank);
  }
  public NativeSQLBuilder addWhereLeEq(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS_EQUAL, fieldAlias);
  }
  public NativeSQLBuilder addWhereLeEq(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_LESS_EQUAL, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereIn(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_IN);
  }
  public NativeSQLBuilder addWhereIn(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_IN, notBlank);
  }
  public NativeSQLBuilder addWhereIn(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_IN, fieldAlias);
  }
  public NativeSQLBuilder addWhereIn(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_IN, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereNotIn(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_NOT_IN);
  }
  public NativeSQLBuilder addWhereNotIn(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_NOT_IN, notBlank);
  }
  public NativeSQLBuilder addWhereNotIn(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_NOT_IN, fieldAlias);
  }
  public NativeSQLBuilder addWhereNotIn(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_NOT_IN, notBlank, fieldAlias);
  }

  public NativeSQLBuilder addWhereRightLike(String prefix, String field, Object value) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_RIGHT_LIKE);
  }
  public NativeSQLBuilder addWhereRightLike(String prefix, String field, Object value, boolean notBlank) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_RIGHT_LIKE, notBlank);
  }
  public NativeSQLBuilder addWhereRightLike(String prefix, String field, Object value, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_RIGHT_LIKE, fieldAlias);
  }
  public NativeSQLBuilder addWhereRightLike(String prefix, String field, Object value, boolean notBlank, String fieldAlias) {
    return addWhere(prefix, field, value, ConditionEnum.OPERATE_RIGHT_LIKE, notBlank, fieldAlias);
  }
}
